﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EXT;
namespace BanHangCshap.Class
{
    class PhieuNhapXuat:Database
    {
        #region ***** Fields & Properties *****
        private string _manhapxuat;
        public string manhapxuat
        {
            get
            {
                return _manhapxuat;
            }
            set
            {
                _manhapxuat = value;
            }
        }
        private string _makho;
        public string makho
        {
            get
            {
                return _makho;
            }
            set
            {
                _makho = value;
            }
        }
        private string _ngaynhapxuat;
        public string ngaynhapxuat
        {
            get
            {
                return _ngaynhapxuat;
            }
            set
            {
                _ngaynhapxuat = value;
            }
        }
        private bool _xuatgiasi;
        public bool xuatgiasi
        {
            get
            {
                return _xuatgiasi;
            }
            set
            {
                _xuatgiasi = value;
            }
        }
        private string _makhncc;
        public string makhncc
        {
            get
            {
                return _makhncc;
            }
            set
            {
                _makhncc = value;
            }
        }
        //private double _nocu;
        //public double nocu
        //{
        //    get
        //    {
        //        return _nocu;
        //    }
        //    set
        //    {
        //        _nocu = value;
        //    }
        //}
        private double _tongtienso;
        public double tongtienso
        {
            get
            {
                return _tongtienso;
            }
            set
            {
                _tongtienso = value;
            }
        }
        private string _tongtienchu;
        public string tongtienchu
        {
            get
            {
                return _tongtienchu;
            }
            set
            {
                _tongtienchu = value;
            }
        }
        private double _ck;
        public double ck
        {
            get
            {
                return _ck;
            }
            set
            {
                _ck = value;
            }
        }
        private double _tienck;
        public double tienck
        {
            get
            {
                return _tienck;
            }
            set
            {
                _tienck = value;
            }
        }
        private double _thanhtoan;
        public double thanhtoan
        {
            get
            {
                return _thanhtoan;
            }
            set
            {
                _thanhtoan = value;
            }
        }
        
        private int _maquy;
        public int maquy
        {
            get
            {
                return _maquy;
            }
            set
            {
                _maquy = value;
            }
        }
        private string _loainhapxuat;
        public string loainhapxuat
        {
            get
            {
                return _loainhapxuat;
            }
            set
            {
                _loainhapxuat = value;
            }
        }
       
        private int _loai;
        public int loai
        {
            get
            {
                return _loai;
            }
            set
            {
                _loai = value;
            }
        }
        private string _tendn;
        public string tendn
        {
            get
            {
                return _tendn;
            }
            set
            {
                _tendn = value;
            }
        }
        private string _ghichunx;
        public string ghichunx
        {
            get
            {
                return _ghichunx;
            }
            set
            {
                _ghichunx = value;
            }
        }
        private int _id;
        public int id
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
            }
        }
        private bool _daxoa;
        public bool daxoa
        {
            get
            {
                return _daxoa;
            }
            set
            {
                _daxoa = value;
            }
        }
        private string _doituonglienhe;
        public string doituonglienhe
        {
            get
            {
                return _doituonglienhe;
            }
            set
            {
                _doituonglienhe = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public PhieuNhapXuat()
        {
        }
        public PhieuNhapXuat(string manhapxuat)
        {
            this.manhapxuat = manhapxuat;
        }
        //public PhieuNhapXuat(string manhapxuat, string ngaynhapxuat, int makhncc, double tongtienchietkhau, double tongtienthue, double tongtienso, string tongtienchu, string loaiphieu, string tendn)
        //{
        //    this.manhapxuat = manhapxuat;
        //    this.ngaynhapxuat = ngaynhapxuat;
        //    this.makhncc = makhncc;
        //    this.tongtienchietkhau = tongtienchietkhau;
        //    this.tongtienthue = tongtienthue;
        //    this.tongtienso = tongtienso;
        //    this.tongtienchu = tongtienchu;
        //    this.loainhapxuat = loainhapxuat;
        //    this.tendn = tendn;
        //}
        #endregion
        public Boolean isExistPhieuNhapXuat(string manhapxuat)
        {
            string sql = "select manhapxuat from PhieuNhapXuat where manhapxuat = '" + manhapxuat + "'";
            return isExist(sql);
        }
        public Boolean themPhieuNhapXuat()
        {
            string sql = "insert into PhieuNhapXuat(manhapxuat,ngaynhapxuat,xuatgiasi,makhncc,ck,tienck,tongtienso,tongtienchu,thanhtoan,maquy,loainhapxuat,loai,tendn,ghichunx,id,daxoa,doituonglienhe,makho) ";
            sql += "values ('" + manhapxuat + "','" + ngaynhapxuat + "','" + xuatgiasi +  "','" + makhncc + "'," + ck + "," + tienck + "," + tongtienso + ",N'" + tongtienchu + "'," + thanhtoan + "," + maquy + ",'" + loainhapxuat + "'," + loai + ",'" + tendn + "',N'" + ghichunx + "'," + id + ",'false',N'" + doituonglienhe + "','" + makho + "')";
            return executeQueryTrans(sql);
            //return executeQuery(sql);
        }
        public Boolean themPhieuNhapXuat1()
        {
            string sql = "insert into PhieuNhapXuat(manhapxuat,ngaynhapxuat,xuatgiasi,makhncc,tongtienso,tongtienchu,thanhtoan,maquy,loainhapxuat,loai,tendn,ghichunx) ";
            sql += "values ('" + manhapxuat + "','" + ngaynhapxuat + "','" + xuatgiasi + "','" + makhncc + "'," + tongtienso + ",N'" + tongtienchu + "'," + thanhtoan + "," + maquy + ",'" + loainhapxuat + "'," + loai + ",'" + tendn + "',N'" + ghichunx + "')";
            return executeQuery(sql);
            //return executeQuery(sql);
        }
        public Boolean xoaPhieuNhapXuat(string manhapxuat)
        {
            string sql = "delete PhieuNhapXuat where manhapxuat = '" + manhapxuat + "'";
            return executeQueryTrans(sql);
        }
        public Boolean xoaTamPhieuNhapXuat(string manhapxuat)
        {
            string sql = "update PhieuNhapXuat set daxoa = 'true' where manhapxuat = '" + manhapxuat + "'";
            return executeQueryTrans(sql);
        }
        public DataTable layDSPhieuNhapXuatTrongNgay(string ngay)
        {
            string sql = "select * from PhieuNhapXuat where daxoa = 'false' and convert(varchar(10),ngaynhapxuat,103) = convert(varchar(10),'" + ngay + "')";
            return getDataTable(sql);
        }
        public DataTable layThongTinPhieuNhapXuat(string manhapxuat)
        {
            string sql = "select distinct manhapxuat,ngaynhapxuat,xuatgiasi,makhncc,doituonglienhe,nocu,ck,tongtienso,tongtienchu,thanhtoan,tendn,loainhapxuat,maquy,loai,ghichunx,id,makho from v_PhieuNhapXuat where manhapxuat = '" + manhapxuat + "'    ";


            return getDataTable(sql);
        }
        public int layTongSoPhieuNhapXuat()
        {
            string sql = "select manhapxuat from PhieuNhapXuat ";
            return getDataTable(sql).Rows.Count;
        }
        public int layIDPhieuNhapXuatMax()
        {
            string sql = "select max(id) from PhieuNhapXuat ";
            return (int)getNumberValue(sql);
        }
        public DataTable layDSPhieuNhapXuatCT(string manhapxuat)
        {
            string sql = "select a.mahang, tenhang, a.dvt,slchuaquydoi,slquydoi, slnhapxuat,quycach,phivanchuyen,dgnhapxuat, chietkhau,thuexuat,thanhtien,chuthich from PhieuNhapXuatCT a, HangHoa b where a.mahang = b.mahang ";
            sql += "and manhapxuat = '" + manhapxuat + "'  order by stt ";
            return getDataTable(sql);
        }
        public DataTable layDSBoSanPhamNhapXuat(string manhapxuat)
        {
            string sql = "select id,a.mabosp, tenbosp,a.dvt,soluong,dongia,chietkhau,thanhtien ";
            sql += "from BoSanPhamNhapXuat a, BoSanPham b where a.mabosp = b.mabosp and manhapxuat = '" + manhapxuat + "' order by id";
            return getDataTable(sql);
        }
        
        public double laySoPhieuNhapXuatTrongNgay(string ngay,string loainhapxuat)
        {
            string sql = "SELECT max(convert(int,substring(manhapxuat,12,len(manhapxuat)-10))) FROM PhieuNhapXuat WHERE convert(varchar(10),ngaynhapxuat,103)='" + ngay + "' and loainhapxuat = '" + loainhapxuat + "'  ";
            return getNumberValue(sql);
        }
        public DataTable timKiemPhieuNhapXuat(int loai, string tungay, string denngay, string makhncc)
        {
            string sql = "select /*distinct N'Kết quả tìm kiếm' as tieude,*/a.manhapxuat,a.makhncc,ngaynhapxuat,xuatgiasi,tenkhncc,tongtienso,loai,daxoa from PhieuNhapXuat a left join KhachHangNCC c on a.makhncc = c.makhncc where daxoa = 'false' and loai = " + loai + " and ";
            sql += " convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            if (makhncc != "")
            {
                sql += "and a.makhncc = '" + makhncc + "' ";
            }
            sql += " order by a.manhapxuat desc";
            return getDataTable(sql);
        }
        #region Report
        public DataTable inPhieuNhapXuat( string tendoanhnghiep, string diachi, string sodt,string manhapxuat)
        {
            ////string sql = "select N'" + tenreport + "' as tenreport,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,a.manhapxuat,ngaynhapxuat,tenkhncc,diachikhncc,sodtkhncc,ghichukhncc,tenhang,b.dvt,dgnhapxuat,slchuaquydoi,thanhtien,chietkhau,tongtienso,tongtienchu,thanhtoan,nocu,tongnoso,tongnochu ";
            ////sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b, KhachHangNCC c,HangHoa d ";
            ////sql += "where a.manhapxuat = '" + manhapxuat + "' and a.manhapxuat = b.manhapxuat and a.makhncc = c.makhncc and b.mahang = d.mahang ";
            ////return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@manhapxuat" };
            //string[] strValue = { thongtindoanhnghiep, manhapxuat };
            //return getDataTableSP("sp_inPhieuNhapXuat", strPara, strValue);
            string sql = "select distinct stt,case when loai = 1 then N'PHIẾU NHẬP HÀNG' ";
			sql += "when loai = 2 then N'PHIẾU TRẢ HÀNG' ";
			sql += "WHEN loai = 3 then N'PHIẾU TRẢ HÀNG' ";
			sql += "WHEN loai = 4 then N'PHIẾU XUẤT HÀNG' END AS tenreport ";
            sql += ",N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi, '" + sodt + "' as sodt,doituonglienhe,manhapxuat,N'Ngày ' + case when len(convert(char(2),day(convert(datetime,ngaynhapxuat,103)))) = 1 then '0' + convert(char(2),day(convert(datetime,ngaynhapxuat,103))) else convert(char(2),day(convert(datetime,ngaynhapxuat,103))) end + N' tháng ' + case when len(convert(char(2),month(convert(datetime,ngaynhapxuat,103)))) = 1 then '0' + convert(char(2),month(convert(datetime,ngaynhapxuat,103))) else convert(char(2),month(convert(datetime,ngaynhapxuat,103))) end  + N' năm ' + convert(char(4),year(ngaynhapxuat)) as ngaynhapxuat,tenkhncc,diachikhncc,sodtkhncc,ghichunx,tenhang,dvt,dgnhapxuat,chuthich,convert(varchar(5),slchuaquydoi) as slchuaquydoi,quycach,phivanchuyen,thanhtien, chietkhau, thuexuat,tienck,tongtienso,tongtienchu,thanhtoan,nocu,tongnoso ";
            sql += " from v_PhieuNhapXuat ";
            sql += "where manhapxuat = '" + manhapxuat + "'  order by stt ";
            return getDataTable(sql);
        }
        
        public DataTable baoCaoXuatNhapTon(string thongtindoanhnghiep, string tungay, string denngay)
        {

            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' - đến ngày: ' + '" + denngay + "' as khoangthoigian,tenchungloai,tenloai,temp1.mahang, tenhang,dvt,temp1.sltondauki,temp1.tgtondauki,slnhaptrongki, tgnhaptrongki,slxuattrongki,tgxuattrongki, ";
            sql += "(temp1.sltondauki + slnhaptrongki - slxuattrongki ) as sltoncuoiki, ";
            sql += "((temp1.sltondauki + slnhaptrongki - slxuattrongki ) * convert(decimal,(case (tgtondauki + tgnhaptrongki) when 0 then gianhap else (tgtondauki + tgnhaptrongki) end) /(case (sltondauki + slnhaptrongki) when 0 then 1 else (sltondauki + slnhaptrongki) end ))) as tgtoncuoiki ";
            //sql += "gianhap * (case when (temp1.sltondauki + slnhaptrongki - slxuattrongki)   < 0 then 0 else (temp1.sltondauki + slnhaptrongki - slxuattrongki) end ) as tgtoncuoiki ";
            sql += "from (select mahang,tenhang,dvt,gianhap, a.maloai,tenloai,b.machungloai,tenchungloai from HangHoa a, LoaiHang b, ChungLoaiHang c where a.maloai = b.maloai and b.machungloai = c.machungloai) temp ";
            sql += "left join ";
            sql += "(select hh.mahang,(slnhap - slxuat) as sltondauki,isnull((slnhap - slxuat) * gianhap,0) as tgtondauki ";
            sql += "from HangHoa hh ";
            sql += "left join ";
            sql += "(select a.mahang,isnull(slnhap,0) as slnhap, isnull(tgnhap,0) as tgnhap  ";
            sql += "from HangHoa a left join ";
            sql += "(select mahang,sum(slnhapxuat) as slnhap, sum(thanhtien) as tgnhap ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) nhap ";
            sql += "on hh.mahang = nhap.mahang ";
            sql += "left join ";
            sql += "(select a.mahang,isnull(slxuat,0) as slxuat, isnull(tgxuat,0) as tgxuat ";
            sql += "from HangHoa a left join ";
            sql += "(select mahang,sum(slnhapxuat) as slxuat, sum(thanhtien) as tgxuat ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) xuat ";
            sql += "on hh.mahang = xuat.mahang) temp1 ";
            sql += "on temp.mahang = temp1.mahang left join ";
            sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
            sql += "from HangHoa a left join ";
            sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and  ";
            sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += " group by mahang) b on a.mahang = b.mahang) temp2 ";
            sql += "on temp.mahang = temp2.mahang left join ";
            sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
            sql += "from HangHoa a left join ";
            sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and  ";
            sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += "group by mahang) b on a.mahang = b.mahang) temp3 ";
            sql += "on temp.mahang = temp3.mahang order by tenloai";
            return getDataTable(sql);

        }
        public DataTable layDSChiTietHangHangBanTheoNgay(string thongtindoanhnghiep, string ngay)
        {
            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,'Ngày: ' + '" + ngay + "' as ngay, ";
            sql += "temp.mahang,tenhang,dvt,tenkhncc,dgnhapxuat,slnhapxuat,chietkhau,thanhtien ";
            sql += "from  ";
            sql += "( ";
	            sql += "select distinct mahang,tenhang,dvt,tenkhncc,dgnhapxuat,slnhapxuat,chietkhau,thanhtien  ";
	            sql += "from v_PhieuNhapXuat where loai = 4 and ";
	            sql += "convert(datetime,ngaynhapxuat,103) = convert(datetime,'" + ngay + "',103)  ";
            sql += ") temp ";
            sql += "order by mahang";
            return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@ngay" };
            //string[] strValue = { thongtindoanhnghiep, ngay };
            //return getDataTableSP("sp_ChiTietHangHoaBanTheoNgay", strPara, strValue);
        }
        public DataTable layDSHangHoaBanTheoNhanVien(string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select distinct N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' - đến ngày: ' +  '" + denngay + "' as khoangthoigian, ";
            sql += "temp.mahang,tenhang,hoten,dvt,isnull(slnhapxuat,0) as slnhapxuat, isnull(sltralai,0) as sltralai,isnull(tghangban,0) as tghangban, isnull(tghangtra,0) as tghangtra ";
            sql += "from  ";
            sql += "( ";
	            sql += "select distinct manhapxuat,mahang,tenhang,dvt,hoten from v_PhieuNhapXuat a, NguoiDung b  ";
	            sql += "where convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103) and loainhapxuat = 'PX' ";
	            sql += "and a.tendn = b.tendn ";
            sql += ") temp left join  ";
            sql += "( ";
	            sql += "select distinct mahang,sum(slnhapxuat) as slnhapxuat,sum(thanhtien) as tghangban ";
	            sql += "from v_PhieuNhapXuat  where loai = 4  and   ";
                sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
	            sql += "group by mahang ";
            	
            sql += ") temp1 on temp.mahang = temp1.mahang left join ";
            sql += "( ";
	            sql += "select distinct mahang,sum(slnhapxuat) as sltralai,sum(thanhtien) as tghangtra ";
	            sql += "from v_PhieuNhapXuat where loai = 2 and   ";
                sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
	            sql += "group by mahang ";
                sql += ") temp2 on temp1.mahang = temp2.mahang";
                return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay" };
            //string[] strValue = { thongtindoanhnghiep, tungay, denngay };
            //return getDataTableSP("sp_HangBanTheoNhanVien", strPara, strValue);
        }
        public DataTable layDSPhieuXuatCTHienThiTrongNhapTonKho(string manhapxuat)
        {

            string sql = "select distinct a.mahang, tenhang,dvt, case when dgnhapxuat is null then gianhap else dgnhapxuat end as dgnhapxuat,isnull(slchuaquydoi,0) as slchuaquydoi,isnull(slnhapxuat,0) as slnhapxuat, ";
            sql += "isnull(thanhtien,0) as thanhtien,isnull(b.chietkhau,0) as chietkhau,isnull(thanhtienchietkhau,0) as thanhtienchietkhau,isnull(b.thue,0) as thue ";
            sql += ",isnull(thanhtienthue,0) as thanhtienthue,isnull(tongtien,0) as tongtien,sltonhientai ";
            sql += "from HangHoa a ";
            sql += "left join   LoaiHang c  on a.maloai = c.maloai  ";
            sql += "left join TonKhoHienTai d on a.mahang = d.mahang  ";
            sql += "left join  ";
            sql += "(select distinct mahang,manhapxuat,dgnhapxuat, slchuaquydoi,slnhapxuat,thanhtien,chietkhau,thanhtienchietkhau,thue,  ";
            sql += "thanhtienthue,thanhtien - thanhtienchietkhau + thanhtienthue as tongtien  ";
            sql += "from PhieuNhapXuatCT where manhapxuat = 'TK20000101-001') b on a.mahang = b.mahang  ";
            return getDataTable(sql);
        }
        public DataTable baoCaoTonKho(Boolean istatca,string thongtindoanhnghiep, string tungay, string denngay, string makho,string tenkho,string machungloai, string maloai, string mahang)
        {
           
            //string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' - đến ngày: ' + '" + denngay + "' as khoangthoigian,tenchungloai,tenloai,temp1.mahang, tenhang,dvt,temp1.sltondauki,temp1.tgtondauki,slnhaptrongki, tgnhaptrongki,slxuattrongki,tgxuattrongki, ";
            //sql += "(temp1.sltondauki + slnhaptrongki - slxuattrongki ) as sltoncuoiki, ";
            //sql += "((temp1.sltondauki + slnhaptrongki - slxuattrongki ) * convert(decimal,(case (tgtondauki + tgnhaptrongki) when 0 then gianhap else (tgtondauki + tgnhaptrongki) end) /(case (sltondauki + slnhaptrongki) when 0 then 1 else (sltondauki + slnhaptrongki) end ))) as tgtoncuoiki ";
            ////sql += "gianhap * (case when (temp1.sltondauki + slnhaptrongki - slxuattrongki)   < 0 then 0 else (temp1.sltondauki + slnhaptrongki - slxuattrongki) end ) as tgtoncuoiki ";
            //sql += "from (select mahang,tenhang,dvt,gianhap, a.maloai,tenloai,b.machungloai,tenchungloai from HangHoa a, LoaiHang b, ChungLoaiHang c where a.maloai = b.maloai and b.machungloai = c.machungloai) temp ";
            //sql += "left join ";
            //sql += "(select hh.mahang,(slnhap - slxuat) as sltondauki,isnull((slnhap - slxuat) * gianhap,0) as tgtondauki ";
            //sql += "from HangHoa hh ";
            //sql += "left join ";
            //sql += "(select a.mahang,isnull(slnhap,0) as slnhap, isnull(tgnhap,0) as tgnhap  ";
            //sql += "from HangHoa a left join ";
            //sql += "(select mahang,sum(slnhapxuat) as slnhap, sum(thanhtien) as tgnhap ";
            //sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) nhap ";
            //sql += "on hh.mahang = nhap.mahang ";
            //sql += "left join ";
            //sql += "(select a.mahang,isnull(slxuat,0) as slxuat, isnull(tgxuat,0) as tgxuat ";
            //sql += "from HangHoa a left join ";
            //sql += "(select mahang,sum(slnhapxuat) as slxuat, sum(thanhtien) as tgxuat ";
            //sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) xuat ";
            //sql += "on hh.mahang = xuat.mahang) temp1 ";
            //sql += "on temp.mahang = temp1.mahang left join ";
            //sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
            //sql += "from HangHoa a left join ";
            //sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
            //sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and  ";
            //sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            //sql += " group by mahang) b on a.mahang = b.mahang) temp2 ";
            //sql += "on temp.mahang = temp2.mahang left join ";
            //sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
            //sql += "from HangHoa a left join ";
            //sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
            //sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and  ";
            //sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            //sql += "group by mahang) b on a.mahang = b.mahang) temp3 ";
            //sql += "on temp.mahang = temp3.mahang order by tenloai";
            ////string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' - đến ngày: ' + '" + denngay + "' as khoangthoigian,tenchungloai,tenloai,temp1.mahang, tenhang,dvt,gianhap,giaxuatle,temp1.sltondauki,temp1.tgtondauki,slnhaptrongki, tgnhaptrongki,slxuattrongki,tgxuattrongki, ";
            ////sql += "(temp1.sltondauki + slnhaptrongki - slxuattrongki ) as sltoncuoiki, ";
            ////sql += "convert(decimal,(case (tgtondauki + tgnhaptrongki) when 0 then gianhap else (tgtondauki + tgnhaptrongki) end) /(case (sltondauki + slnhaptrongki) when 0 then 1 else (sltondauki + slnhaptrongki) end )) as gianhaptb, ";
            ////sql += "convert(decimal,(case (tgtondauki + tgxuattrongki) when 0 then giaxuatle else (tgtondauki + tgxuattrongki) end) /(case (sltondauki + slxuattrongki) when 0 then 1 else (sltondauki + slxuattrongki) end )) as giaxuattb ,";
            ////sql += "((temp1.sltondauki + slnhaptrongki - slxuattrongki ) * convert(decimal,(case (tgtondauki + tgnhaptrongki) when 0 then gianhap else (tgtondauki + tgnhaptrongki) end) /(case (sltondauki + slnhaptrongki) when 0 then 1 else (sltondauki + slnhaptrongki) end ))) as tgtoncuoiki ";

            ////sql += "from (select mahang,tenhang,dvt,gianhap,giaxuatle, a.maloai,tenloai,b.machungloai,tenchungloai from HangHoa a, LoaiHang b, ChungLoaiHang c where a.maloai = b.maloai and b.machungloai = c.machungloai ";
            ////if (machungloai.Trim().Equals("") == false)
            ////    sql += "and b.machungloai = '" + machungloai.Trim() + "' ";
            ////if (maloai.Trim().Equals("") == false)
            ////    sql += "and b.maloai = '" + maloai.Trim() + "' ";
            ////if (mahang.Trim().Equals("") == false)
            ////    sql += "and a.mahang = '" + mahang.Trim() + "' ";

            ////sql += ") temp ";
            ////sql += "left join ";
            ////sql += "(select hh.mahang,(slnhap - slxuat) as sltondauki,isnull((slnhap - slxuat) * (select dgnhapxuat from PhieuNhapXuatCT where mahang = hh.mahang and manhapxuat = (select distinct max(manhapxuat) from v_PhieuNhapXuat where convert(DateTime,ngaynhapxuat,103) <convert(DateTime,'" + tungay + "',103) and mahang = hh.mahang)),0) as tgtondauki ";
            ////sql += "from HangHoa hh ";
            ////sql += "left join ";
            ////sql += "(select a.mahang,isnull(slnhap,0) as slnhap, isnull(tgnhap,0) as tgnhap  ";
            ////sql += "from HangHoa a left join ";
            ////sql += "(select mahang,sum(slnhapxuat) as slnhap, sum(thanhtien) as tgnhap ";
            ////sql += "from PhieuNhapXuat a, v_PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) nhap ";
            ////sql += "on hh.mahang = nhap.mahang ";
            ////sql += "left join ";
            ////sql += "(select a.mahang,isnull(slxuat,0) as slxuat, isnull(tgxuat,0) as tgxuat ";
            ////sql += "from HangHoa a left join ";
            ////sql += "(select mahang,sum(slnhapxuat) as slxuat, sum(thanhtien) as tgxuat ";
            ////sql += "from PhieuNhapXuat a, v_PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) xuat ";
            ////sql += "on hh.mahang = xuat.mahang) temp1 ";
            ////sql += "on temp.mahang = temp1.mahang left join ";
            ////sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
            ////sql += "from HangHoa a left join ";
            ////sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
            ////sql += "from PhieuNhapXuat a, v_PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and  ";
            ////sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            ////sql += " group by mahang) b on a.mahang = b.mahang) temp2 ";
            ////sql += "on temp.mahang = temp2.mahang left join ";
            ////sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
            ////sql += "from HangHoa a left join ";
            ////sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
            ////sql += "from PhieuNhapXuat a, v_PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and  ";
            ////sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            ////sql += "group by mahang) b on a.mahang = b.mahang) temp3 ";
            ////sql += "on temp.mahang = temp3.mahang order by tenloai,tenhang";
            DataTable dt = null;
            if (istatca)
            {
                string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay", "@makho","@tenkho", "@machungloai", "@maloai", "@mahang" };
                string[] strValue = { thongtindoanhnghiep, tungay, denngay, makho,tenkho, machungloai, maloai, mahang };
                dt = getDataTableSP("sp_TonKho1", strPara, strValue);
            }
            else
            {
                string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay", "@makho", "@tenkho", "@machungloai", "@maloai", "@mahang" };
                string[] strValue = { thongtindoanhnghiep, tungay, denngay, makho,tenkho, machungloai, maloai, mahang };
                dt = getDataTableSP("sp_TonKho", strPara, strValue);
            }
            return dt;
        }
        #endregion
        public DataTable inPhieuNhapXuatTongHop(string tendoanhnghiep, string diachi, string sodt, string makhncc, string tungay, string denngay, string doituonglienhe)
        {
            string sql = "select khoangthoigian,tendoanhnghiep,diachi,sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe, manhapxuat,ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien,thuexuat,chietkhau,tongtienso,tongtienchu,thanhtoan,stt,nocu,tongthanhtien,tongthanhtoan, nocu + tongthanhtien - tongthanhtoan as tongno ";
            sql += "from ( ";
            sql += "select N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi,'" + sodt + "' as sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe,ghichunx,isnull((select distinct conno  from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and sort = (select distinct max(sort) from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and convert(DateTime,ngay,103) < convert(DateTime,'" + tungay + "',103) )),0) as nocu,isnull((select sum(thu) from v_ThuChi where makhncc = '" + makhncc + "' and convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)),0) as tongthanhtoan,manhapxuat, convert(varchar(10),ngaynhapxuat,103) as ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien, chietkhau, thuexuat,tongtienso,tongtienchu,thanhtoan,stt, ";
            //sql += "(select sum(thanhtoan) from v_PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103))  as tongthanhtoan, ";
            sql += "(select sum(tongtienso) from PhieuNhapXuat where makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)) as tongthanhtien ";
            sql += "from v_PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "'  and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";

            sql += ") temp order by manhapxuat,stt ";
            return getDataTable(sql);
            
        }
        public DataTable inPhieuNhapXuatTongHop(string tendoanhnghiep, string diachi, string sodt, string makhncc, string tungay, string denngay)
        {
            string sql = "select N'PHIẾU XUẤT HÀNG TỔNG HỢP' reportname,loaihang,khoangthoigian,tendoanhnghiep,diachi,sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe, manhapxuat,ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien,thuexuat,chietkhau,tongtienso,tongtienchu,thanhtoan,stt,nocu,tongthanhtien,isnull(tongthanhtoan,0) as tongthanhtoan,isnull(tonghangtra,0) as tonghangtra, nocu + tongthanhtien - isnull(tongthanhtoan,0)- isnull(tonghangtra,0) as tongno ,sort ";
            sql += "from ( ";
            sql += "select N'Hàng mua' as loaihang,N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi,'" + sodt + "' as sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe,ghichunx,isnull((select distinct conno  from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and sort = (select distinct max(sort) from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and convert(DateTime,ngay,103) < convert(DateTime,'" + tungay + "',103) )),0) as nocu,isnull((select sum(thu) from v_ThuChi where makhncc = '" + makhncc + "' and convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)),0) as tongthanhtoan,isnull((select sum(tongtienso)-sum(thanhtoan) from PhieuNhapXuat where makhncc = '" + makhncc + "' and loai = 2 and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)),0) as tonghangtra,manhapxuat, convert(varchar(10),ngaynhapxuat,103) as ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien, chietkhau, thuexuat,tongtienso,tongtienchu,thanhtoan,stt,sort, ";
            //sql += "(select sum(thanhtoan) from v_PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103))  as tongthanhtoan, ";
            sql += "(select sum(tongtienso) from PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)) as tongthanhtien ";
            sql += "from v_PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "'  and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";
            sql += "union all ";
            sql += "select N'Hàng trả' as loaihang,N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi,'" + sodt + "' as sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe,ghichunx,isnull((select distinct conno  from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and sort = (select distinct max(sort) from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and convert(DateTime,ngay,103) < convert(DateTime,'" + tungay + "',103) )),0) as nocu,isnull((select sum(thu) from v_ThuChi where makhncc = '" + makhncc + "' and convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)),0) as tongthanhtoan,isnull((select sum(tongtienso)-sum(thanhtoan) from PhieuNhapXuat where makhncc = '" + makhncc + "' and loai = 2 and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)),0) as tonghangtra,manhapxuat, convert(varchar(10),ngaynhapxuat,103) as ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien, chietkhau, thuexuat,tongtienso,tongtienchu,thanhtoan,stt,sort, ";
            //sql += "(select sum(thanhtoan) from v_PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103))  as tongthanhtoan, ";
            sql += "(select sum(tongtienso) from PhieuNhapXuat where loai = 4 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)) as tongthanhtien ";
            sql += "from v_PhieuNhapXuat where loai = 2 and makhncc = '" + makhncc + "'  and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";
            sql += ") temp order by loaihang,sort ";
            return getDataTable(sql);
        }

        //Hiện giờ đọc chưa nổi câu trên nên em xin coppy lại và sữa 4 thành 1 và 2 thành 3
        public DataTable inPhieuNhapTongHop(string tendoanhnghiep, string diachi, string sodt, string makhncc, string tungay, string denngay)
        {
            string sql = "select N'PHIẾU NHẬP HÀNG TỔNG HỢP' reportname,loaihang,khoangthoigian,tendoanhnghiep,diachi,sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe, manhapxuat,ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien,thuexuat,chietkhau,tongtienso,tongtienchu,thanhtoan,stt,nocu,tongthanhtien,isnull(tongthanhtoan,0) as tongthanhtoan,isnull(tonghangtra,0) as tonghangtra, nocu + tongthanhtien - isnull(tongthanhtoan,0)- isnull(tonghangtra,0) as tongno ,sort ";
            sql += "from ( ";
            sql += "select N'Hàng mua' as loaihang,N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi,'" + sodt + "' as sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe,ghichunx,isnull((select distinct conno  from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and sort = (select distinct max(sort) from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and convert(DateTime,ngay,103) < convert(DateTime,'" + tungay + "',103) )),0) as nocu,isnull((select sum(thu) from v_ThuChi where makhncc = '" + makhncc + "' and convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)),0) as tongthanhtoan,isnull((select sum(tongtienso)-sum(thanhtoan) from PhieuNhapXuat where makhncc = '" + makhncc + "' and loai = 3 and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)),0) as tonghangtra,manhapxuat, convert(varchar(10),ngaynhapxuat,103) as ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien, chietkhau, thuexuat,tongtienso,tongtienchu,thanhtoan,stt,sort, ";
            //sql += "(select sum(thanhtoan) from v_PhieuNhapXuat where loai = 1 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103))  as tongthanhtoan, ";
            sql += "(select sum(tongtienso) from PhieuNhapXuat where loai = 1 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)) as tongthanhtien ";
            sql += "from v_PhieuNhapXuat where loai = 1 and makhncc = '" + makhncc + "'  and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";
            sql += "union all ";
            sql += "select N'Hàng trả' as loaihang,N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi,'" + sodt + "' as sodt,makhncc,tenkhncc,diachikhncc,sodtkhncc,doituonglienhe,ghichunx,isnull((select distinct conno  from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and sort = (select distinct max(sort) from v_TheoDoiCongNoKHNCC where makhncc = '" + makhncc + "' and convert(DateTime,ngay,103) < convert(DateTime,'" + tungay + "',103) )),0) as nocu,isnull((select sum(thu) from v_ThuChi where makhncc = '" + makhncc + "' and convert(DateTime,ngaythuchi,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaythuchi,103) <= convert(DateTime,'" + denngay + "',103)),0) as tongthanhtoan,isnull((select sum(tongtienso)-sum(thanhtoan) from PhieuNhapXuat where makhncc = '" + makhncc + "' and loai = 3 and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)),0) as tonghangtra,manhapxuat, convert(varchar(10),ngaynhapxuat,103) as ngaynhapxuat,tenhang,dvt,dgnhapxuat,slchuaquydoi,thanhtien, chietkhau, thuexuat,tongtienso,tongtienchu,thanhtoan,stt,sort, ";
            //sql += "(select sum(thanhtoan) from v_PhieuNhapXuat where loai = 1 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103))  as tongthanhtoan, ";
            sql += "(select sum(tongtienso) from PhieuNhapXuat where loai = 1 and makhncc = '" + makhncc + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103)) as tongthanhtien ";
            sql += "from v_PhieuNhapXuat where loai = 3 and makhncc = '" + makhncc + "'  and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";
            sql += ") temp order by loaihang,sort ";
            return getDataTable(sql);
        }


        public DataTable inHangKhuyenMai(string thongtindoanhnghiep, string loainhapxuat,string tieude,string tungay, string denngay)
        {
            string sql = "select N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep, N'" + tieude + "' as tieude, convert(char(10),ngaynhapxuat,103) as ngaynhapxuat,tenloai,a.mahang, tenhang, b.dvt,sum(slnhapxuat) as soluong,sum(dgnhapxuat) as dongia, sum(thanhtien) as  thanhtien,gianhap,sum(gianhap * slnhapxuat) as trigianhap from PhieuNhapXuatCT a, HangHoa b,LoaiHang c,PhieuNhapXuat d where a.mahang = b.mahang and b.maloai = c.maloai and a.manhapxuat = d.manhapxuat ";
            sql += "  and khuyenmai = 'true' and loainhapxuat = '" + loainhapxuat + "' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";
            sql += "group by ngaynhapxuat,tenloai,a.mahang, tenhang, b.dvt,gianhap order by ngaynhapxuat ";

            return getDataTable(sql);
        }
        public DataTable inDoanhThuTuChietKhau(string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,manhapxuat,convert(char(10),ngaynhapxuat,103) as ngaynhapxuat,tenkhncc,tongtienso,ck,tienck from v_PhieuNhapXuat ";
            sql += "where loainhapxuat = 'PN' and convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) ";
            

            return getDataTable(sql);
        }
        public DataTable layDSHangHoaTrongPhieuXuat(string tungay, string denngay)
        {
            string sql = "select distinct a.manhapxuat,b.mahang,tenhang from PhieuNhapXuat a, PhieuNhapXuatCT b, HangHoa c where a.manhapxuat = b.manhapxuat and b.mahang = c.mahang and ";
            sql += "convert(DateTime,ngaynhapxuat,103) >= convert(DateTime,'" + tungay + "',103) and convert(DateTime,ngaynhapxuat,103) <= convert(DateTime,'" + denngay + "',103) and loainhapxuat = 'PX'";
            return getDataTable(sql);
        }

        public DataTable layPhieunhapTonghop(string makhncc, string Tungay, string Denngay)
        {
            string strSp = "sp_Nhaphangtonghop";
            string [] strPara = new string[3];
            object[] strValue = new object[3];

            strPara[0] = "@Tungay";
            strPara[1] = "@Denngay";
            strPara[2] = "@Makhncc";

            strValue[0] = Tungay.ToDateTime();
            strValue[1] = Denngay.ToDateTime();
            strValue[2] = makhncc;

            return getDataTableSP(strSp, strPara, strValue);
        }
        
       
       

      

    }
}
